1. Field of the Invention
This invention relates in general to database management systems performed by computers. In particular, it concerns a technique for enforcing temporal uniqueness in an object or relational database management system environment.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. An Object Relational Database Management System (ORDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving objects. These databases are organized into tables which consist of rows and columns of objects, or data. A database will typically have many tables and each table will typically have multiple rows and columns.
ORDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software. SQL has been adopted as such by both the American National Standards Organization (ANSI) and the International Standards Organization (ISO).
All data in ORDBMS software is externally structured into tables. The SQL interface allows users to formulate relational operations on tables. This can be done either interactively, in batch files, or embedded in host language, such as C, COBOL, .etc. Operators are provided in SQL that allow the user to manipulate the data. The power of SQL lies in its ability to link information from multiple tables or views together to perform complex sets of procedures with a single statement.
Temporal data is often used to track the period of time at which certain business conditions are valid. Several common forms of data analysis involve evaluating time-related data. Examples are examining customer buying behavior, assessing the effectiveness of marketing campaigns, and determining the impact of organizational changes on sales during a selected time period.
It is often desirable when working with temporal data to ensure integrity for time periods in which conditions will be valid. Unfortunately, conventional techniques used to support uniqueness of non-temporal data fail to work when temporal data is involved. This is because they do not include software needed for verifying and enforcing the temporal uniqueness constraint. For example, a firm may wish to have its DBMS enforce the temporal uniqueness constraint so that xe2x80x9cemployees cannot earn multiple salaries during the same period of timexe2x80x9d or xe2x80x9cdepartments can only report to one division during any given period of time.xe2x80x9d
In non-temporal DBMS environments, data uniqueness is often guaranteed by defining one or more columns to serve as the primary key and/or defining a unique index on the appropriate columns. However, because commercial object or relational DBMS do not understand the underlying semantics of temporal data, these techniques cannot enforce temporal uniqueness. Thus, data that is considered invalid from a business point of view can be readily stored in a DBMS environment without error. This would violate the integrity rules that a firm may wish to enforce and render subsequent analysis of its data misleading or useless.
While there have been various techniques developed for enforcing temporal uniqueness in a DBMS environment, there is a need in the art for an approach which is cost-effective for vendors to implement and that integrates easily into existing object or relational DBMS environments of customers.
The foregoing and other objects, features, and advantages of the present invention will be apparent from the following detailed description of the preferred embodiments, which makes reference to several figures.
The present invention enforces temporal integrity for valid time data. The technique consists of implementing user-defined functions with templates for triggers and setting constraints to dictate the conditions for the temporal data. Together, these support many forms of temporal analysis and help enforce the fundamental aspects of temporal integrity. Among these aspects are temporal uniqueness, which refers to data that is time period exclusive.
The present invention ensures the integrity of temporal data in object or relational database management systems. Temporal and non-temporal data are organized into data objects in databases, each data object comprised of at least one row and at least one column. A data object may also be a table containing data. When a user attempts to insert new data into a data object in which temporal uniqueness is a consideration, or when a user attempts to update existing data in a data object in which temporal uniqueness is a consideration, the present invention operates to provide a barrier against inserts or updates that would violate the temporal integrity of the existing data.
All functions include error-checking logic to verify that the start and end points of a time period constitute a valid time period. For example, if the database uses dates to track temporal data and employs a xe2x80x9cclosed, openxe2x80x9d temporal representation, start dates should be less than end dates. Such error-checking logic is built into the functions. However, to avoid populating temporal tables with invalid time periods, it is recommended that CHECK constraints be defined on relevant time-related columns (such as columns that contain date information).
The present invention helps users enforce temporal uniqueness by providing trigger templates and CHECK constraints.. One preferred embodiment includes a combination of CHECK constraints and triggers. Since determining the correct logic to implement and successfully code this logic can be a rather challenging and error-prone experience for many users, the present invention simplifies the process by providing templates in a sample database. By substituting appropriate table and column names for those provided with the sample database, users can easily instruct the DBMS to enforce temporal uniqueness for those tables in which they deem such a constraint to be important.
The present invention may also be packaged as a DBMS system extender. The extender may be provided to users of ORDBMS systems desiring to enforce temporal integrity of data within their system environments.